﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ReadWriteExcel
{
    public static class ClsExcelUtility
    {
        private static IWorkbook hssfWorkbook;


        //TODO:可以导出超过65535行的记录,采用拆分表的形式。
        /// <summary>  
        /// 使用DataTable导出数据  
        /// </summary>  
        /// <param name="dt">数据</param>  
        /// <param name="FileName">文件名</param>  
        /// <param name="msg">提示消息</param>  
        public static void DataTableToExcel65535(DataTable dt, string FileName)
        {
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    var dlg = new SaveFileDialog(); //申明保存对话框  
                    dlg.Filter = "Excel文件|*.xls|Excel文件|*.xlsx";
                    dlg.FileName = FileName; //默认文件名称  
                    if (dlg.ShowDialog() == DialogResult.Cancel)
                        return;

                    dlg.InitialDirectory = Directory.GetCurrentDirectory(); //返回文件路径  
                    var filename = dlg.FileName; //输出的文件名称  
                    if (filename.Trim() == " ") //验证strFileName是否为空或值无效  
                        return;

                    hssfWorkbook = new HSSFWorkbook();

                    //                    var sheet = hssfWorkbook.CreateSheet("Sheet1");
                    var sheet = hssfWorkbook.CreateSheet("Sheet");
                    var row = sheet.CreateRow(0); //创建列头数据  
                    var displayColumnsCount = 0; //用于处理隐藏列头不被显示           

                    var hssffont = hssfWorkbook.CreateFont() as HSSFFont;
                    hssffont.FontHeightInPoints = 11;

                    //生成表头信息  
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        var cell = row.CreateCell(displayColumnsCount);
                        //设置标题信息
                        cell.SetCellValue(dt.Columns[i].ToString());
                        //设置列宽
                        sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
                        displayColumnsCount++;

                    }

                    //记录额外创建的sheet数量
                    var index = 0;
                    //设置单元格边框   
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        if ((i + 1) % 65535 == 0)
                        {
                            sheet = hssfWorkbook.CreateSheet("Sheet" + index);
                            row = sheet.CreateRow(0);
                            int displayColumnsCount1 = 0; //用于处理隐藏列头不被显示           
                                                          //                            hssffont = hssfWorkbook.CreateFont() as HSSFFont;
                                                          //                            hssffont.FontHeightInPoints = 11;

                            //生成表头信息  
                            for (var kk = 0; kk < dt.Columns.Count; kk++)
                            {
                                var cell = row.CreateCell(displayColumnsCount1);
                                //设置标题信息
                                cell.SetCellValue(dt.Columns[kk].ToString());
                                //设置列宽
                                sheet.SetColumnWidth(displayColumnsCount1, 17 * 256);
                                displayColumnsCount1++;
                            }

                            index++;
                        }

                        //从第二行开始
                        //var irow = sheet.CreateRow(i + 1);

                        if (index == 0)
                        {
                            var irow = sheet.CreateRow((i + 1) - (index * 65535));
                            for (var j = 0; j <= dt.Columns.Count - 1; j++)
                            {
                                var cell = irow.CreateCell(j);
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                        else
                        {
                            var irow = sheet.CreateRow((i + 1 + 1) - (index * 65535));
                            for (var j = 0; j <= dt.Columns.Count - 1; j++)
                            {
                                var cell = irow.CreateCell(j);
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }

                    }

                    using (Stream stream = File.OpenWrite(filename))
                    {
                        hssfWorkbook.Write(stream);
                        MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("没有数据可供导出", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作失败", "提示");
            }
        }
        /// <summary>
        /// 获取excel内容
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string filePath)
        {
            DataTable dt = new DataTable();
            using (FileStream fsRead = System.IO.File.OpenRead(filePath))
            {
                IWorkbook wk = null;
                //获取后缀名
                string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                //判断是否是excel文件
                if (extension == ".xlsx" || extension == ".xls")
                {
                    //判断excel的版本
                    if (extension == ".xlsx")
                    {
                        wk = new XSSFWorkbook(fsRead);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fsRead);
                    }

                    //获取第一个sheet
                    ISheet sheet = wk.GetSheetAt(0);
                    //获取第一行
                    IRow headrow = sheet.GetRow(0);
                    //创建列
                    for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
                    {
                        //  DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
                        DataColumn datacolum = new DataColumn("F" + (i + 1));
                        dt.Columns.Add(datacolum);
                    }
                    //读取每行,从第二行起
                    for (int r = 1; r <= sheet.LastRowNum; r++)
                    {
                        bool result = false;
                        DataRow dr = dt.NewRow();
                        //获取当前行
                        IRow row = sheet.GetRow(r);
                        //读取每列
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            ICell cell = row.GetCell(j); //一个单元格
                            dr[j] = GetCellValue(cell); //获取单元格的值
                            //全为空则不取
                            if (dr[j].ToString() != "")
                            {
                                result = true;
                            }
                        }
                        if (result == true)
                        {
                            dt.Rows.Add(dr); //把每行追加到DataTable
                        }
                    }
                }

            }
            return dt;
        }

        //对单元格进行判断取值
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                    return string.Empty;
                case CellType.Boolean: //bool类型
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric: //数字类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return cell.DateCellValue.ToString();
                    }
                    else //其它数字
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();//
                case CellType.String: //string 类型
                    return cell.StringCellValue;
                case CellType.Formula: //带公式类型
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }
        /// <summary>  
        /// 将excel导入到datatable  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }

        }

        static IWorkbook hssfworkbook;

        /// <summary>  
        /// 使用DataTable导出数据  
        /// </summary>  
        /// <param name="dt">数据</param>  
        /// <param name="FileName">文件名</param>  
        /// <param name="msg">提示消息</param>  
        public static void DataTableToExcel(DataTable dt, string FileName)
        {
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    SaveFileDialog dlg = new SaveFileDialog(); //申明保存对话框  
                    dlg.Filter = "Excel文件|*.xls";
                    dlg.FileName = FileName; //默认文件名称  
                    if (dlg.ShowDialog() == DialogResult.Cancel)
                        return;

                    dlg.InitialDirectory = Directory.GetCurrentDirectory(); //返回文件路径  
                    string filename = dlg.FileName; //输出的文件名称  
                    if (filename.Trim() == " ") //验证strFileName是否为空或值无效  
                    {
                        return;
                    }

                    hssfworkbook = new HSSFWorkbook();
                    ISheet sheet = hssfworkbook.CreateSheet("Sheet1");

                    int colscount = dt.Columns.Count; //定义表格内数据的列数                 
                    ArrayList arr = new ArrayList(); //储存头信息  
                    ICellStyle cstyle = hssfworkbook.CreateCellStyle();

                    IRow rowHeader = sheet.CreateRow(0); //创建列头数据  
                    int displayColumnsCount = 0; //用于处理隐藏列头不被显示           

                    HSSFFont hssffont = hssfworkbook.CreateFont() as HSSFFont;
                    hssffont.FontHeightInPoints = 11;

                    //设置单元格边框   
                    for (int i = 0; i < dt.Columns.Count; i++) //生成表头信息  
                    {
                        ICell cell = rowHeader.CreateCell(displayColumnsCount);
                        cell.SetCellValue(dt.Columns[i].ToString());
                        sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
                        displayColumnsCount++;
                        arr.Add(dt.Columns[i].ToString());
                    }

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow irow = sheet.CreateRow(i + 1);
                        //for (int j = 0; j <= dt.Columns.Count - 1; j++)
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = irow.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }

                    using (Stream stream = File.OpenWrite(filename))
                    {
                        hssfworkbook.Write(stream);
                        MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("没有数据可供导出", "提示");
                }
            }
            catch
            {
                MessageBox.Show("操作失败", "提示");
            }
        }

    }



}
